﻿namespace HelperLibrary;

public class NPOIHelper
{
    public static void ExportToExcel<T>(List<T> data, string head, string sheetName, string filepath)
    {
        IWorkbook wb = new HSSFWorkbook();
        //设置工作簿的名称
        sheetName = string.IsNullOrEmpty(sheetName) ? "sheet1" : sheetName;
        //创建一个工作簿
        ISheet sh = wb.CreateSheet(sheetName);

        //全局索引
        int gloal_index = 0;
        System.Reflection.PropertyInfo[] oProps = null;

        ICellStyle headCellStyle = Getcellstyle(wb, "head");
        ICellStyle cellStyle = Getcellstyle(wb, "");
        foreach (T en in data)
        {
            if (oProps == null)
            {
                oProps = ((Type)en.GetType()).GetProperties();
            }
            if (gloal_index == 0)
            {
                #region 表头
                //合并单元格
                sh.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, oProps.Length - 1));
                //创建第1行
                IRow row0 = sh.CreateRow(0);
                //设置第1行高度
                row0.Height = 20 * 20;
                //创建第1行第1列
                ICell icell1top0 = row0.CreateCell(0);
                //设置第1行第1列格式
                icell1top0.CellStyle = headCellStyle;
                //设置第1行第1列内容
                icell1top0.SetCellValue(head);
                #endregion

                #region 抬头
                //创建第2行
                IRow row1 = sh.CreateRow(1);
                //设置高度
                row1.Height = 20 * 20;
                //columnt_index是列的索引
                int columnt_index = 0;
                foreach (System.Reflection.PropertyInfo item in oProps)
                {
                    //获取T的字段名称
                    string name = item.Name;
                    //获取T的字段名称的描述
                    string des = ((DescriptionAttribute)Attribute.GetCustomAttribute(item, typeof(DescriptionAttribute))).Description;

                    //创建第2行的第columnt_index列
                    ICell icell1top = row1.CreateCell(columnt_index);
                    //设置第2行的第columnt_index列的格式
                    icell1top.CellStyle = cellStyle;
                    //设置第2行的第columnt_index列的内容
                    if (!string.IsNullOrEmpty(des))
                    {
                        icell1top.SetCellValue(des);
                    }
                    else
                    {
                        icell1top.SetCellValue(name);
                    }
                    //设置第2行的第columnt_index列的宽度
                    sh.SetColumnWidth(columnt_index, (int)((15 + 0.72) * 256));
                    columnt_index++;
                }
                #endregion

                gloal_index = 2;
            }
            #region 数据
            //创建第gloal_index行
            IRow row_zs = sh.CreateRow(gloal_index);
            int column_index = 0;
            foreach (System.Reflection.PropertyInfo pi in oProps)
            {
                //创建第gloal_index行的第columnt_index列
                ICell icell1top = row_zs.CreateCell(column_index);
                //设置第gloal_index行的第columnt_index列格式
                icell1top.CellStyle = cellStyle;
                //获取en字段值
                string v_value = pi.GetValue(en, null) == null ? "" : pi.GetValue(en, null).ToString();
                //设置第gloal_index行的第columnt_index列的内容
                icell1top.SetCellValue(v_value);
                column_index++;
            }
            #endregion

            gloal_index++;
        }

        if (File.Exists(filepath))
            File.Delete(filepath);
        using (FileStream fs = new FileStream(filepath, FileMode.Create, FileAccess.Write))
        {
            wb.Write(fs);
        }
    }

    public static void ExportToExcel(Dictionary<string, List<string[]>> dic, string filepath)
    {
        try
        {
            IWorkbook wb;
            if (filepath.IndexOf(".xlsx") > 0)//Excel 2007 
                wb = new XSSFWorkbook();
            else //Excel 2003
                wb = new HSSFWorkbook();
            foreach (KeyValuePair<string, List<string[]>> keyValuePair in dic)
            {
                string sheetName = keyValuePair.Key;
                List<string[]> data = keyValuePair.Value;
                //创建一个工作簿
                ISheet sh = wb.CreateSheet(sheetName);

                var i = 0;
                for (i = 0; i < data.Count; i++)
                {
                    var row = sh.CreateRow(i);
                    for (var j = 0; j < data[i].Length; ++j)
                    {
                        row.CreateCell(j).SetCellValue(data[i][j]);
                    }
                }
            }

            if (File.Exists(filepath))
                File.Delete(filepath);
            using (FileStream fs = new FileStream(filepath, FileMode.Create, FileAccess.Write))
            {
                wb.Write(fs);
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public static void ExportToMergeRowExcel(Dictionary<string, List<string[]>> dic, Dictionary<string, List<string>> mergedic,string filepath)
    {
        try
        {
            IWorkbook wb;
            if (filepath.IndexOf(".xlsx") > 0)//Excel 2007 
                wb = new XSSFWorkbook();
            else //Excel 2003
                wb = new HSSFWorkbook();
            foreach (KeyValuePair<string, List<string[]>> keyValuePair in dic)
            {
                string sheetName = keyValuePair.Key;
                List<string[]> data = keyValuePair.Value;
                //创建一个工作簿
                ISheet sh = wb.CreateSheet(sheetName);
                sh.SetColumnWidth(9, 200 * 20);
                sh.SetColumnWidth(10, 200 * 20);
                sh.SetColumnWidth(11, 200 * 20);
                sh.SetColumnWidth(12, 200 * 20);
                sh.SetColumnWidth(13, 200 * 20);
                sh.SetColumnWidth(14, 200 * 20);
                var i = 0;
                for (i = 0; i < data.Count + 1; i++)
                {
                    if (i == 0)
                    {
                        var row = sh.CreateRow(i);
                        ICellStyle tableHeadStyle = wb.CreateCellStyle();
                        tableHeadStyle.Alignment = HorizontalAlignment.Center;
                        tableHeadStyle.VerticalAlignment = VerticalAlignment.Center;
                        row.RowStyle = tableHeadStyle;
                        var negIndex = 0;
                        for (var j = 0; j < data[i].Length; ++j)
                        {
                            if (data[i][j] == "负面情绪" )
                            {
                                row.CreateCell(j).SetCellValue(data[i][j]);
                                for (int z = 0; z < mergedic[data[i][j]].Count - 1; z++)
                                {
                                    row.CreateCell(z);
                                }
                                negIndex = j;
                                int firstCol = j;
                                int lastCol = firstCol + mergedic[data[i][j]].Count - 1;
                                //15-20  6
                                sh.AddMergedRegion(new CellRangeAddress(0, 0, firstCol, lastCol));
                            }
                            else if (data[i][j] == "正面情绪")
                            {
                                for (int z = 0; z < mergedic[data[i][j]].Count - 1; z++)
                                {
                                    row.CreateCell(z);
                                }
                                int firstCol = negIndex + mergedic["负面情绪"].Count;
                                int lastCol = firstCol + mergedic[data[i][j]].Count - 1;
                                //21-23  3
                                row.CreateCell(firstCol).SetCellValue(data[i][j]);
                                sh.AddMergedRegion(new CellRangeAddress(0, 0, firstCol, lastCol));
                            }
                            else if (data[i][j] == "心理能力")
                            {
                                for (int z = 0; z < mergedic[data[i][j]].Count - 1; z++)
                                {
                                    row.CreateCell(z);
                                }
                                //24-28   5
                                int firstCol = negIndex + mergedic["负面情绪"].Count + mergedic["正面情绪"].Count;
                                int lastCol = firstCol + mergedic[data[i][j]].Count - 1;
                                row.CreateCell(firstCol).SetCellValue(data[i][j]);
                                sh.AddMergedRegion(new CellRangeAddress(0, 0, firstCol, lastCol));
                            }
                            else
                            {
                                var cell = row.CreateCell(j);
                            }
                        }
                    }
                    else if (i == 1)
                    {
                        var row = sh.CreateRow(i);
                        var mergeColCount = mergedic["负面情绪"].Count + mergedic["正面情绪"].Count + mergedic["心理能力"].Count;
                        var list = data[0].ToList();
                        var last = data[0][data[0].Length - 1];
                        list.RemoveAt(data[0].Length - 1);
                        list.Remove("负面情绪");
                        list.Remove("正面情绪");
                        list.Remove("心理能力");
                        list.AddRange(mergedic["负面情绪"]);
                        list.AddRange(mergedic["正面情绪"]);
                        list.AddRange(mergedic["心理能力"]);
                        list.Add(last);
                        var first_row = sh.GetRow(0);
                        for (var j = 0; j < list.Count; ++j)
                        {
                            row.CreateCell(j).SetCellValue(list[j]);
                            if (j <= 14)
                            {
                                first_row.GetCell(j).SetCellValue(list[j]);
                                sh.AddMergedRegion(new CellRangeAddress(0, 1, j, j));
                            }
                            else if (j == list.Count - 1)
                            {
                                first_row.CreateCell(29).SetCellValue(list[j]);
                                sh.AddMergedRegion(new CellRangeAddress(0, 1, j, j));
                            }
                        }
                    }
                    else
                    {
                        var row = sh.CreateRow(i);
                        for (var j = 0; j < data[i - 1].Length; ++j)
                        {
                            row.CreateCell(j).SetCellValue(data[i - 1][j]);
                        }
                    }
                }
            }

            if (File.Exists(filepath))
                File.Delete(filepath);
            using (FileStream fs = new FileStream(filepath, FileMode.Create, FileAccess.Write))
            {
                wb.Write(fs);
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public static void ExportToMergeRowExcel2(Dictionary<string, List<string[]>> dic, Dictionary<string, List<string>> mergedic, string filepath)
    {
        try
        {
            IWorkbook wb;
            if (filepath.IndexOf(".xlsx") > 0)//Excel 2007 
                wb = new XSSFWorkbook();
            else //Excel 2003
                wb = new HSSFWorkbook();
            foreach (KeyValuePair<string, List<string[]>> keyValuePair in dic)
            {
                string sheetName = keyValuePair.Key;
                List<string[]> data = keyValuePair.Value;
                //创建一个工作簿
                ISheet sh = wb.CreateSheet(sheetName);
                sh.SetColumnWidth(10, 200 * 20);
                sh.SetColumnWidth(11, 200 * 20);
                sh.SetColumnWidth(12, 200 * 20);
                sh.SetColumnWidth(13, 200 * 20);
                sh.SetColumnWidth(14, 200 * 20);
                sh.SetColumnWidth(15, 200 * 20);
                var i = 0;
                for (i = 0; i < data.Count + 1; i++)
                {
                    if (i == 0)
                    {
                        var row = sh.CreateRow(i);
                        ICellStyle tableHeadStyle = wb.CreateCellStyle();
                        tableHeadStyle.Alignment = HorizontalAlignment.Center;
                        tableHeadStyle.VerticalAlignment = VerticalAlignment.Center;
                        row.RowStyle = tableHeadStyle;
                        var negIndex = 0;
                        for (var j = 0; j < data[i].Length; ++j)
                        {
                            if (data[i][j] == "负面情绪")
                            {
                                row.CreateCell(j).SetCellValue(data[i][j]);
                                for (int z = 0; z < mergedic[data[i][j]].Count - 1; z++)
                                {
                                    row.CreateCell(z);
                                }
                                negIndex = j;
                                int firstCol = j;
                                int lastCol = firstCol + mergedic[data[i][j]].Count - 1;
                                //15-20  6
                                sh.AddMergedRegion(new CellRangeAddress(0, 0, firstCol, lastCol));
                            }
                            else if (data[i][j] == "正面情绪")
                            {
                                for (int z = 0; z < mergedic[data[i][j]].Count - 1; z++)
                                {
                                    row.CreateCell(z);
                                }
                                int firstCol = negIndex + mergedic["负面情绪"].Count;
                                int lastCol = firstCol + mergedic[data[i][j]].Count - 1;
                                //21-23  3
                                row.CreateCell(firstCol).SetCellValue(data[i][j]);
                                sh.AddMergedRegion(new CellRangeAddress(0, 0, firstCol, lastCol));
                            }
                            else if (data[i][j] == "心理能力")
                            {
                                for (int z = 0; z < mergedic[data[i][j]].Count - 1; z++)
                                {
                                    row.CreateCell(z);
                                }
                                //24-28   5
                                int firstCol = negIndex + mergedic["负面情绪"].Count + mergedic["正面情绪"].Count;
                                int lastCol = firstCol + mergedic[data[i][j]].Count - 1;
                                row.CreateCell(firstCol).SetCellValue(data[i][j]);
                                sh.AddMergedRegion(new CellRangeAddress(0, 0, firstCol, lastCol));
                            }
                            else
                            {
                                var cell = row.CreateCell(j);
                            }
                        }
                    }
                    else if (i == 1)
                    {
                        var row = sh.CreateRow(i);
                        var mergeColCount = mergedic["负面情绪"].Count + mergedic["正面情绪"].Count + mergedic["心理能力"].Count;
                        var list = data[0].ToList();
                        var last = data[0][data[0].Length - 1];
                        list.RemoveAt(data[0].Length - 1);
                        list.Remove("负面情绪");
                        list.Remove("正面情绪");
                        list.Remove("心理能力");
                        list.AddRange(mergedic["负面情绪"]);
                        list.AddRange(mergedic["正面情绪"]);
                        list.AddRange(mergedic["心理能力"]);
                        list.Add(last);
                        var first_row = sh.GetRow(0);
                        for (var j = 0; j < list.Count; ++j)
                        {
                            row.CreateCell(j).SetCellValue(list[j]);
                            if (j <= 15)
                            {
                                first_row.GetCell(j).SetCellValue(list[j]);
                                sh.AddMergedRegion(new CellRangeAddress(0, 1, j, j));
                            }
                            else if (j == list.Count - 1)
                            {
                                first_row.CreateCell(30).SetCellValue(list[j]);
                                sh.AddMergedRegion(new CellRangeAddress(0, 1, j, j));
                            }
                        }
                    }
                    else
                    {
                        var row = sh.CreateRow(i);
                        for (var j = 0; j < data[i - 1].Length; ++j)
                        {
                            row.CreateCell(j).SetCellValue(data[i - 1][j]);
                        }
                    }
                }
            }

            if (File.Exists(filepath))
                File.Delete(filepath);
            using (FileStream fs = new FileStream(filepath, FileMode.Create, FileAccess.Write))
            {
                wb.Write(fs);
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    private static ICellStyle Getcellstyle(IWorkbook wb, string type)
    {
        ICellStyle cellStyle = wb.CreateCellStyle();
        //定义字体  
        IFont font = wb.CreateFont();
        font.FontName = "微软雅黑";
        //水平对齐  
        cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
        //垂直对齐  
        cellStyle.VerticalAlignment = VerticalAlignment.Center;
        //自动换行  
        cellStyle.WrapText = true;
        //缩进
        cellStyle.Indention = 0;

        switch (type)
        {
            case "head":
                cellStyle.SetFont(font);
                cellStyle.Alignment = HorizontalAlignment.Center;
                break;
            default:
                cellStyle.SetFont(font);
                break;
        }
        return cellStyle;
    }

    /// <summary>
    /// 导出EXECL
    /// </summary>
    public static byte[] OutputExcel<T>(List<T> entitys, string[] title)
    {
        IWorkbook workbook = new XSSFWorkbook();
        ISheet sheet = workbook.CreateSheet("sheet");
        IRow Title = null;
        IRow rows = null;
        Type entityType = null;
        PropertyInfo[] entityProperties = null;
        if (entitys.Count > 0)
        {
            entityType = entitys[0].GetType();
            entityProperties = entityType.GetProperties();
        }

        for (int i = 0; i <= entitys.Count; i++)
        {
            if (i == 0)
            {
                Title = sheet.CreateRow(0);
                for (int k = 0; k < title.Length; k++)
                {
                    ICell cell = Title.CreateCell(k);
                    cell.SetCellValue(title[k]);
                }
                continue;
            }
            else
            {
                rows = sheet.CreateRow(i);
                object entity = entitys[i - 1];
                for (int j = 0; j < entityProperties.Length; j++)
                {
                    object[] entityValues = new object[entityProperties.Length];
                    entityValues[j] = entityProperties[j].GetValue(entity);
                    if (entityValues[j] == null)
                    {
                        entityValues[j] = "";
                    }
                    rows.CreateCell(j).SetCellValue(entityValues[j].ToString());
                }

            }
        }

        byte[] buffer = null;//new byte[1024 * 2];
        using (MemoryStream ms = new MemoryStream())
        {
            workbook.Write(ms);
            buffer = ms.ToArray();//ms.GetBuffer();
            ms.Close();
        }

        return buffer;
    }

    /// <summary>
    /// 导出EXECL(多个sheet)
    /// </summary>
    public static void OutputExcelBySheets<T>(Dictionary<string, List<T>> data, Dictionary<string, string[]> head, string filepath)
    {
        IWorkbook workbook;
        if (filepath.IndexOf(".xlsx") > 0)//Excel 2007 
            workbook = new XSSFWorkbook();
        else //Excel 2003
            workbook = new HSSFWorkbook();
        IRow Title = null;
        IRow rows = null;
        Type entityType = null;

        //单元格样式
        ICellStyle cellStyle = workbook.CreateCellStyle();
        IDataFormat dataformat = workbook.CreateDataFormat();
        cellStyle.DataFormat = dataformat.GetFormat("text");
        IFont f = workbook.CreateFont();
        cellStyle.SetFont(f);
        foreach (KeyValuePair<string, List<T>> keyValuePair in data)
        {
            string sheetName = keyValuePair.Key;
            List<T> entitys = keyValuePair.Value;
            var title = head[sheetName];
            ISheet sheet = workbook.CreateSheet(sheetName);
            sheet.SetColumnWidth(0, 20 * 256);
            PropertyInfo[] entityProperties = null;
            if (entitys.Count > 0)
            {
                entityType = entitys[0].GetType();
                entityProperties = entityType.GetProperties();
            }
            for (int i = 0; i <= entitys.Count; i++)
            {
                if (i == 0)
                {
                    Title = sheet.CreateRow(0);
                    for (int k = 0; k < title.Length; k++)
                    {
                        ICell cell = Title.CreateCell(k);
                        cell.SetCellValue(title[k]);
                        cell.CellStyle = cellStyle;
                    }
                    continue;
                }
                else
                {
                    rows = sheet.CreateRow(i);
                    object entity = entitys[i - 1];
                    for (int j = 0; j < entityProperties.Length; j++)
                    {
                        object[] entityValues = new object[entityProperties.Length];
                        entityValues[j] = entityProperties[j].GetValue(entity);
                        if (entityValues[j] == null)
                        {
                            entityValues[j] = "";
                        }
                        ICell bodyCell = rows.CreateCell(j);
                        bodyCell.SetCellValue(entityValues[j].ToString());
                        bodyCell.CellStyle = cellStyle;
                    }
                }
            }
        }

        if (File.Exists(filepath))
            File.Delete(filepath);
        using (FileStream fs = new FileStream(filepath, FileMode.Create, FileAccess.Write))
        {
            workbook.Write(fs);
        }
    }

    public static Dictionary<string, List<string[]>> ImportFromExcel(MemoryStream ms, string filename, bool removefirst = true)
    {
        try
        {
            Dictionary<string, List<string[]>> dic = new Dictionary<string, List<string[]>>();
            IWorkbook workbook = null;
            if (filename.IndexOf(".xlsx") > 0) //Excel 2007
                workbook = new XSSFWorkbook(ms);
            else //Excel 2007
                workbook = new HSSFWorkbook(ms);

            for (int k = 0; k < workbook.NumberOfSheets; k++)
            {
                ISheet sheet = workbook.GetSheetAt(k);
                IRow cellNum = sheet.GetRow(0);
                
                List<string[]> liststr = new List<string[]>();
                string value = null;
                int f = 0;
                if (removefirst)
                    f = 1;
                for (int i = f; i <= sheet.LastRowNum; i++)
                {
                    int num = cellNum.Cells.Count;
                    IRow row = sheet.GetRow(i);
                    string[] arrystr = new string[num];
                    for (int j = 0; j < num; j++)
                    {
                        if (row.GetCell(j) == null)
                        {
                            value = "";
                        }
                        else
                        {
                            if (row.GetCell(j).CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(row.GetCell(j)))
                            {
                                if (row.GetCell(j).DateCellValue != null)
                                {
                                    value = row.GetCell(j).DateCellValue.Value.ToYMD();
                                }
                            }
                            else
                            {
                                value = row.GetCell(j).ToString();
                            }
                        }
                        arrystr[j] = value;
                    }

                    liststr.Add(arrystr);
                }
                dic.Add(sheet.SheetName, liststr);
            }
            return dic;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
}
